﻿Imports System.Data.SqlClient
Public Class clsInventario
  Private cnDB As SqlConnection

  Public Sub New(ByVal psConnectString As String)
    cnDB = New SqlConnection(psConnectString)
  End Sub

  Public Function InventarioListar(Optional ByRef psdFecIni As DateTime = Nothing, Optional ByRef psdFecFin As DateTime = Nothing, Optional ByRef pcEstado As String = Nothing) As DataTable
    Dim sqlCmd As New SqlClient.SqlCommand
    Dim oSqlda As SqlDataAdapter
    Dim oDataTable As New DataTable
    Try
      sqlCmd.Connection = cnDB
      sqlCmd.CommandType = CommandType.StoredProcedure
      sqlCmd.CommandText = "spInventarioListar"
      sqlCmd.Parameters.Add("@psdFecIni", SqlDbType.SmallDateTime).Value = IIf(psdFecIni = Nothing, DBNull.Value, psdFecIni)
      sqlCmd.Parameters.Add("@psdFecFin", SqlDbType.SmallDateTime).Value = IIf(psdFecFin = Nothing, DBNull.Value, psdFecFin)
      sqlCmd.Parameters.Add("@pcEstado", SqlDbType.Char, 1).Value = IIf(pcEstado = Nothing, DBNull.Value, pcEstado)
      cnDB.Open()

      oSqlda = New SqlDataAdapter(sqlCmd)
      oSqlda.Fill(oDataTable)
      oSqlda.Dispose()
      sqlCmd.Dispose()

      Return oDataTable

    Catch ex As Exception
      Throw
    Finally
      If cnDB.State = ConnectionState.Open Then cnDB.Close()
    End Try
  End Function

  Public Function InventarioInsertar(ByVal piNumInv As Int32, ByVal pcNumInv As String, ByVal psiCodAlm As Int16, ByVal psiCodResp As Int16, _
                                ByVal psiCodAprob As Int16, ByVal psdFecInv As DateTime, ByVal pcEstado As String, ByVal odtDetalleInventario As DataTable) As String
    Dim sqlcmd As New SqlCommand
    Dim sqltran As SqlTransaction = Nothing
    Try
      cnDB.Open()
      sqltran = cnDB.BeginTransaction("TranInv")

      sqlcmd.Connection = cnDB
      sqlcmd.Transaction = sqltran
      sqlcmd.CommandType = CommandType.StoredProcedure
      sqlcmd.CommandText = "spInventarioInsertar"

      sqlcmd.Parameters.Add("@piNumInv", SqlDbType.Int)
      sqlcmd.Parameters("@piNumInv").Direction = ParameterDirection.Output
      sqlcmd.Parameters.Add("@pcNumInv", SqlDbType.Char, 14)
      sqlcmd.Parameters("@pcNumInv").Direction = ParameterDirection.Output
      sqlcmd.Parameters.Add("@psiCodAlm", SqlDbType.SmallInt).Value = psiCodAlm
      sqlcmd.Parameters.Add("@psiCodResp", SqlDbType.SmallInt).Value = psiCodResp
      sqlcmd.Parameters.Add("@psiCodAprob", SqlDbType.SmallInt).Value = psiCodAprob
      sqlcmd.Parameters.Add("@psdFecInv", SqlDbType.SmallDateTime).Value = psdFecInv
      sqlcmd.Parameters.Add("@pcEstado", SqlDbType.Char, 1).Value = pcEstado
      sqlcmd.ExecuteNonQuery()

      piNumInv = sqlcmd.Parameters("@piNumInv").Value
      pcNumInv = sqlcmd.Parameters("@pcNumInv").Value

      ' Agregar Detalle del Inventario
      For Each oDataRow In odtDetalleInventario.Rows
        Try
          sqlcmd.Parameters.Clear()

          sqlcmd.CommandText = "spInventarioDetalleInsertar"

          sqlcmd.Parameters.Add("@piNumInv", SqlDbType.Int).Value = piNumInv
          sqlcmd.Parameters.Add("@psiItem", SqlDbType.SmallInt).Value = oDataRow("siItem")
          sqlcmd.Parameters.Add("@psiCodProd", SqlDbType.SmallInt).Value = oDataRow("siCodProd")
          sqlcmd.Parameters.Add("@psiCantidad", SqlDbType.SmallInt).Value = oDataRow("siCantidad")
          sqlcmd.Parameters.Add("@ptiUniMed", SqlDbType.TinyInt).Value = oDataRow("tiUniMed")
          sqlcmd.Parameters.Add("@pvUniMed", SqlDbType.VarChar, 30).Value = oDataRow("vUniMed")
          sqlcmd.Parameters.Add("@pvObsInv", SqlDbType.VarChar, 300).Value = oDataRow("vObsInv")
          sqlcmd.ExecuteNonQuery()


        Catch ex As Exception
          Throw New Exception(ex.Message)
        Finally
          GC.Collect()
        End Try
      Next

      ' Attempt to commit the transaction.
      sqltran.Commit()

      Return pcNumInv

    Catch ex As Exception
      sqltran.Rollback()
      Throw New Exception(ex.Message)
    Finally
      If cnDB.State = ConnectionState.Open Then cnDB.Close()
      GC.Collect()
    End Try
  End Function

  Public Sub InventarioActualizar(ByVal piNumInv As Int32, ByVal psiCodAlm As Int16, ByVal psiCodResp As Int16, ByVal psiCodAprob As Int16, _
                                  ByVal psdFecInv As DateTime, ByVal pcEstado As String, ByVal odtDetalleInventario As DataTable)
    Dim sqlcmd As New SqlCommand
    'Dim sqltran As SqlTransaction = Nothing
    Try
      cnDB.Open()
      'sqltran = cnDB.BeginTransaction("TranInv")

      sqlcmd.Connection = cnDB
      ' sqlcmd.Transaction = sqltran
      sqlcmd.CommandType = CommandType.StoredProcedure
      sqlcmd.CommandText = "spInventarioActualizar"

      sqlcmd.Parameters.Add("@piNumInv", SqlDbType.Int).Value = piNumInv
      sqlcmd.Parameters.Add("@psiCodAlm", SqlDbType.SmallInt).Value = psiCodAlm
      sqlcmd.Parameters.Add("@psiCodResp", SqlDbType.SmallInt).Value = psiCodResp
      sqlcmd.Parameters.Add("@psiCodAprob", SqlDbType.SmallInt).Value = psiCodAprob
      sqlcmd.Parameters.Add("@psdFecInv", SqlDbType.SmallDateTime).Value = psdFecInv
      sqlcmd.Parameters.Add("@pcEstado", SqlDbType.Char, 1).Value = pcEstado
      sqlcmd.ExecuteNonQuery()

      ' Agregar Detalle del Inventario

      For Each oDataRow In odtDetalleInventario.Rows
        Try
          sqlcmd.Parameters.Clear()

          sqlcmd.CommandText = "spInventarioDetalleInsertar"

          sqlcmd.Parameters.Add("@piNumInv", SqlDbType.Int).Value = piNumInv
          sqlcmd.Parameters.Add("@psiItem", SqlDbType.SmallInt).Value = oDataRow("siItem")
          sqlcmd.Parameters.Add("@psiCodProd", SqlDbType.SmallInt).Value = oDataRow("siCodProd")
          sqlcmd.Parameters.Add("@psiCantidad", SqlDbType.SmallInt).Value = oDataRow("siCantidad")
          sqlcmd.Parameters.Add("@ptiUniMed", SqlDbType.TinyInt).Value = oDataRow("tiUniMed")
          sqlcmd.Parameters.Add("@pvUniMed", SqlDbType.VarChar, 30).Value = oDataRow("vUniMed")
          sqlcmd.Parameters.Add("@pvObsInv", SqlDbType.VarChar, 300).Value = oDataRow("vObsInv")
          sqlcmd.ExecuteNonQuery()


        Catch ex As Exception
          Throw New Exception(ex.Message)
        Finally
          GC.Collect()
        End Try
      Next

      ' Attempt to commit the transaction.
      'sqltran.Commit()

    Catch ex As Exception
      'sqltran.Rollback()
      Throw New Exception(ex.Message)
    Finally
      If cnDB.State = ConnectionState.Open Then cnDB.Close()
      GC.Collect()
    End Try
  End Sub

  Public Sub InventarioEliminar(ByVal piNumInv As Int32)
    Dim sqlcmd As New SqlCommand
    Try
      cnDB.Open()

      sqlcmd.Connection = cnDB
      sqlcmd.CommandType = CommandType.StoredProcedure
      sqlcmd.CommandText = "spInventarioEliminar"

      sqlcmd.Parameters.Add("@piNumInv", SqlDbType.Int).Value = piNumInv
      sqlcmd.ExecuteNonQuery()

    Catch ex As Exception
      Throw New Exception(ex.Message)
    Finally
      If cnDB.State = ConnectionState.Open Then cnDB.Close()
      GC.Collect()
    End Try
  End Sub
End Class
